package com.skp.shaphan.connect;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import com.skp.job.ProjectJobException;
import com.skp.shaphan.savedConnections.SavedConnection;

public class Derby extends BasicConnect {

	public Derby(SavedConnection conn) {
		super(conn);
	}

	@Override
	public Connection connect(Properties props) {
		Connection connection = null;
		
		// grab the options
		String optnPath = (String) props.get("databasePath");
		boolean optnCreateDatabase = (props.get("create").toString().equalsIgnoreCase("yes"));
		boolean optnBuildPSDB = (props.get("psTesting").toString().equalsIgnoreCase("yes"));
		
		// ignore the create option if the database already exists
		File path = new File(optnPath);
		if(path.exists()) {
			optnCreateDatabase = false;
		}
		
		// Build the connection string first
		String cs = "jdbc:derby:" + optnPath;
		if(optnCreateDatabase) {
			cs = cs + ";create=true";
		}
		
		
		// Connect
		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
		} catch (ClassNotFoundException e) {
			(new ProjectJobException(e)).displayException();
		}
		try {
			connection = DriverManager.getConnection(cs);
		} catch (SQLException e) {
			(new ProjectJobException(e)).displayException();
		}
		
		if(optnCreateDatabase && optnBuildPSDB) {
			try {
				PreparedStatement pst;
				Statement st = connection.createStatement(ResultSet.FETCH_FORWARD, ResultSet.CONCUR_READ_ONLY);
				st.execute("CREATE TABLE PSRECDEFN (RECNAME VARCHAR(15) NOT NULL,"+
                           "FIELDCOUNT SMALLINT NOT NULL,"+
                           "INDEXCOUNT SMALLINT NOT NULL,"+
                           "DDLCOUNT SMALLINT NOT NULL,"+
                           "VERSION INT NOT NULL,"+
                           "AUDITRECNAME VARCHAR(15) NOT NULL,"+
                           "RECUSE SMALLINT NOT NULL,"+
                           "RECTYPE SMALLINT NOT NULL,"+
                           "SETCNTRLFLD VARCHAR(18) NOT NULL,"+
                           "RELLANGRECNAME VARCHAR(15) NOT NULL,"+
                           "OPTDELRECNAME VARCHAR(15) NOT NULL,"+
                           "RECDESCR VARCHAR(30) NOT NULL,"+
                           "PARENTRECNAME VARCHAR(15) NOT NULL,"+
                           "QRYSECRECNAME VARCHAR(15) NOT NULL,"+
                           "SQLTABLENAME VARCHAR(18) NOT NULL,"+
                           "BUILDSEQNO SMALLINT NOT NULL,"+
                           "OPTTRIGFLAG VARCHAR(1) NOT NULL,"+
                           "OBJECTOWNERID VARCHAR(4) NOT NULL,"+
                           // fix PSDATETIME
                           "LASTUPDDTTM TIMESTAMP,"+
                           "LASTUPDOPRID VARCHAR(30) NOT NULL,"+
                           "SYSTEMIDFIELDNAME VARCHAR(18) NOT NULL,"+
                           "TIMESTAMPFIELDNAME VARCHAR(18) NOT NULL,"+
                           "AUXFLAGMASK INT NOT NULL,"+
                           // fix VARCHAR(MAX)
				"DESCRLONG CLOB)");
				
				st.execute("CREATE TABLE PSPRSMDEFN (PORTAL_NAME VARCHAR(30) NOT NULL, " +
						"   PORTAL_REFTYPE VARCHAR(1) NOT NULL, " +
						"   PORTAL_OBJNAME VARCHAR(30) NOT NULL, " +
						"   PORTAL_PRNTOBJNAME VARCHAR(30) NOT NULL, " +
						"   PORTAL_LABEL VARCHAR(30) NOT NULL, " +
						"   PORTAL_URI_SEG1 VARCHAR(30) NOT NULL, " +
						"   PORTAL_URI_SEG2 VARCHAR(30) NOT NULL, " +
						"   PORTAL_URI_SEG3 VARCHAR(30) NOT NULL, " +
						"   PORTAL_URI_SEG4 VARCHAR(30) NOT NULL) ");
				pst = connection.prepareStatement("INSERT INTO PSPRSMDEFN (PORTAL_NAME, PORTAL_REFTYPE, PORTAL_OBJNAME, PORTAL_PRNTOBJNAME, PORTAL_LABEL, PORTAL_URI_SEG1, PORTAL_URI_SEG2, PORTAL_URI_SEG3, PORTAL_URI_SEG4) VALUES (?,?,?,?,?,?,?,?,?)");
				pst.setString(1, "EMPLOYEE");
				pst.setString(2, "C");
				pst.setString(3, "PT_PORTAL_OBJ_LIST");
				pst.setString(4, "PT_PORTAL");
				pst.setString(5, "Structure and Content");
				pst.setString(6, "PORTAL_ADMIN");
				pst.setString(7, "PORTAL_OBJ_LIST");
				pst.setString(8, "GBL");
				pst.setString(9, " ");
				pst.execute();
				pst.setString(1, "EMPLOYEE");
				pst.setString(2, "F");
				pst.setString(3, "PT_PORTAL");
				pst.setString(4, "PT_PEOPLETOOLS");
				pst.setString(5, "Portal");
				pst.setString(6, " ");
				pst.setString(7, " ");
				pst.setString(8, " ");
				pst.setString(9, " ");
				pst.execute();
				pst.setString(1, "EMPLOYEE");
				pst.setString(2, "F");
				pst.setString(3, "PT_PEOPLETOOLS");
				pst.setString(4, "PORTAL_ROOT_OBJECT");
				pst.setString(5, "PeopleTools");
				pst.setString(6, " ");
				pst.setString(7, " ");
				pst.setString(8, " ");
				pst.setString(9, " ");
				pst.execute();
				pst.setString(1, "EMPLOYEE");
				pst.setString(2, "F");
				pst.setString(3, "PORTAL_ROOT_OBJECT");
				pst.setString(4, " ");
				pst.setString(5, "Root");
				pst.setString(6, " ");
				pst.setString(7, " ");
				pst.setString(8, " ");
				pst.setString(9, " ");
				pst.execute();
				
				
			} catch (SQLException e) {
				(new ProjectJobException(e)).displayException();
			}
		}
		return connection;
	}
}
